import sqlite3
import time
import hashlib


class wordlist_core():
    def __init__(self, sqlpath, init=False):
        # 连接（初始化）数据库
        # __init__() should return None, not 'int'
        self.listdb = sqlite3.connect(sqlpath)
        self.cur = self.listdb.cursor()

        if init:
            sql = "create table main( \
                    uuid char(40) unique, word char(40) PRIMARY KEY, \
                    mean char(50), addition char(100),star int ,flag int,search_times int, \
                    appear_times int,unknown_times int,insert_date char(10),flag_date char(10) \
                    )"
            self.cur.execute(sql)
            self.listdb.commit()

    '''sqlite table main
    |uuid unique|word index|mean|addition|star|flag|search_times|appear_times|unknown_times|insert_date|flag_date|
    '''

    def addword(self, word, mean, addition, star=False):
        # 增加词条
        insert_date = time.strftime("%Y-%m-%d")
        uuid = hashlib.md5((word+insert_date).encode()).hexdigest()
        if star:
            star = 1
        else:
            star = 0
        sql = "insert into main (uuid,word,mean,addition,star,flag, \
                search_times,appear_times,unknown_times,insert_date,flag_date) \
                values (?,?,?,?,?,?,?,?,?,?,?)"
        param = (uuid, word, mean, addition, star, 0,
                 0, 0, 0, insert_date, '0000-00-00')
        try:
            self.cur.execute(sql, param)
            self.listdb.commit()
        except:
            return 1
        return 0

    def selectword(self, word=False, mean=False):
        # 查找词条，可模糊查找word与mean
        if word:
            sql = "select * from main where word like ?"
            param = ('%'+word+'%',)
            try:
                self.cur.execute(sql, param)
            except:
                return 1
        elif mean:
            sql = "select * from main where mean like ?"
            param = ('%'+mean+'%',)
            try:
                self.cur.execute(sql, param)
            except:
                return 1
        else:
            return 1
        return self.cur.fetchall()

    def modifword(self, word, mean=False, addition=False, star=False, remove=False):
        # 修改词条基本信息与删除词条
        sql = "select * from main where word=?"
        param = (word,)
        self.cur.execute(sql, param)
        origin = self.cur.fetchone()
        if origin == None:
            return 2

        if mean == False:
            mean = origin[2]
        if addition == False:
            addition = origin[3]
        if star == False:
            star = origin[4]

        if remove:
            sql = "delete from main where word=?"
            param = (word,)
            try:
                self.cur.execute(sql, param)
                self.listdb.commit()
            except:
                return 1
            return 0

        try:
            sql = "update main set mean=?,addition=?,star=? where word=?"
            param = (mean, addition, star, word)
            self.cur.execute(sql, param)
            self.listdb.commit()
        except:
            return 1

        return 0

    def changetimes(self, uuid, flag=False, search_times=False, appear_times=False, unknown_times=False):
        # 更改词条背诵属性
        sql = "select * from main where uuid=?"
        param = (uuid,)
        self.cur.execute(sql, param)
        origin = self.cur.fetchone()
        if origin == None:
            return 2

        if flag:
            flag = 1
            flag_date = time.strftime("%Y-%m-%d")
        else:
            flag = 0
            flag_date = '0000-00-00'
        if search_times:
            search_times = origin[6]+1
        else:
            search_times = origin[6]
        if appear_times:
            appear_times = origin[7]+1
        else:
            appear_times = origin[7]
        if unknown_times:
            unknown_times = origin[8]+1
        else:
            unknown_times = origin[8]

        try:
            sql = "update main set flag=?,search_times=?,appear_times=?, \
                unknown_times=?,flag_date=? where uuid=?"
            param = (flag, search_times, appear_times,
                     unknown_times, flag_date, uuid)
            self.cur.execute(sql, param)
            self.listdb.commit()
        except:
            return 1

        return 0

    def listword(self, n=0):
        # 给出背诵列表
        if n>0:
            sql = "select * from main order by appear_times,RANDOM(),unknown_times desc,search_times desc,flag limit " \
                +str(n)
        else:
            sql = "select * from main order by appear_times,RANDOM(),unknown_times desc,search_times desc,flag"
        try:
            self.cur.execute(sql)
        except:
            return 1
        return self.cur.fetchall()

    def info(self):
        self.cur.execute("select count(*) from main")
        n_all=self.cur.fetchone()
        self.cur.execute("select count(*) from main where flag=1")
        n_known=self.cur.fetchone()
        return (n_all[0],n_known[0])